﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ProgNetComponentsDx.Options;
using System.Windows.Forms;

namespace SqlCeManager.Tools
{
    public class ScriptTools
    {
        private SchemaDataSet _SchemaDataSet;
        public SchemaDataSet SchemaDataSet
        {
            get { return _SchemaDataSet; }
        }
        public ScriptTools(SchemaDataSet ds)
        {
            _SchemaDataSet = ds;
        }
        public bool GenerateCreateScripts
        {
            get;
            set;
        }
        
        public List<string> GetScripts(string tableName)
        {
            string filter = null;
            if (tableName.IsNotNull())
                filter = "TABLE_NAME = " + tableName.EscapeSql();

            DataRow[] rows = SchemaDataSet.TABLES.Select(filter, null, DataViewRowState.CurrentRows | DataViewRowState.Deleted);

            List<string> allScripts = new List<string>();

            List<string> dropTables = new List<string>();
            List<string> createAlterTables = new List<string>();

            List<string> dropIndex = new List<string>();
            List<string> createIndex = new List<string>();
            List<string> dropFK = new List<string>();
            List<string> createFk = new List<string>();

            foreach (DataRow r in rows)
            {
                string table = "";
                if (r.RowState == DataRowState.Deleted)
                    table = r["TABLE_NAME", DataRowVersion.Original].AsString();
                else
                    table = r["TABLE_NAME"].AsString();

                if (!GenerateCreateScripts)
                    dropTables.AddRange(GetDropTableScripts(table));

                createAlterTables.AddRange(GetCreateOrAlterTableScripts(table));

                if (!GenerateCreateScripts)
                    dropIndex.AddRange(GetDropIndexScripts(table));
                
                createIndex.AddRange(GetCreateIndexScripts(table));

                if (!GenerateCreateScripts)
                    dropFK.AddRange(GetDropFkScripts(table));

                createFk.AddRange(GetCreateFKScripts(table));
            }
            allScripts.AddRange(dropFK);
            allScripts.AddRange(dropIndex);
            allScripts.AddRange(dropTables);

            allScripts.AddRange(createAlterTables);
            allScripts.AddRange(createIndex);
            allScripts.AddRange(createFk);

            return allScripts;
        }

        public List<string> GetScripts()
        {
            return GetScripts(null);
        }
        #region TABLE
        private List<string> GetDropTableScripts(string table)
        {
            List<string> list = new List<string>();
            DataRow row = SchemaDataSet.TABLES.SelectFirstRow("TABLE_NAME = " + table.EscapeSql(), null, DataViewRowState.Added | DataViewRowState.ModifiedCurrent | DataViewRowState.Deleted | DataViewRowState.Unchanged);
            if (row != null)
            {
                if (row.RowState == DataRowState.Deleted)
                    list.Add("DROP TABLE " + table + ";");
            }
            return list;
        }
        private List<string> GetCreateOrAlterTableScripts(string table)
        {
            List<string> list = new List<string>();
            DataRow row = SchemaDataSet.TABLES.SelectFirstRow("TABLE_NAME = " + table.EscapeSql(), null, DataViewRowState.Added | DataViewRowState.ModifiedCurrent | DataViewRowState.Deleted | DataViewRowState.Unchanged);
            if (row != null)
            {
                if (row.RowState != DataRowState.Deleted)
                {
                    if (row.RowState == DataRowState.Added || GenerateCreateScripts )
                        list.Add(GetCreateTableScript(table));
                    else
                        list.AddRange(GetAlterTableScripts(table));
                }
            }
            return list;
        }

        private string GetCreateTableScript(string TableName)
        {
            string str = "CREATE TABLE " + TableName + "(";
            DataRow[] columns = SchemaDataSet.COLUMNS.Select("TABLE_NAME = " + TableName.EscapeSql(), null, DataViewRowState.CurrentRows);
            string colStr = "";
            int idx = 0;
            foreach (DataRow r in columns)
            {
                colStr = GetCreateColumnScript(r);

                idx++;
                if (idx < columns.Length)
                    colStr += ",";
                str = str.AppendLine(colStr);
            }
            str = str.AppendLine(")");
            return str + ";";
        }
        private List<string> GetAlterTableScripts(string TableName)
        {
            List<string> list = new List<string>();
            DataRow[] columns = GetColumns(TableName, DataViewRowState.Deleted);
            foreach (DataRow r in columns)
                list.Add("ALTER TABLE " + TableName + " DROP COLUMN " + r["COLUMN_NAME", DataRowVersion.Original].AsString() + ";");

            columns = GetColumns(TableName, DataViewRowState.Added);
            foreach (DataRow r in columns)
                list.Add("ALTER TABLE " + TableName + " ADD " + GetCreateColumnScript(r) + ";");

            columns = GetColumns(TableName, DataViewRowState.ModifiedCurrent);
            string alterColStr = "";
            bool DontCheckModified = false;
            foreach (DataRow r in columns)
            {
                DontCheckModified = r.ColumnChanged("DATA_TYPE");

                alterColStr = GetAlterColumnDropDefaultScript(r, DontCheckModified);
                if (alterColStr.IsNotNull())
                    list.Add("ALTER TABLE " + TableName + " " + alterColStr + ";");

                alterColStr = GetAlterColumnScript(r);
                if (alterColStr.IsNotNull())
                    list.Add("ALTER TABLE " + TableName + " " + alterColStr + ";");

                alterColStr = GetAlterColumnSetDefaultScript(r, DontCheckModified);
                if (alterColStr.IsNotNull())
                    list.Add("ALTER TABLE " + TableName + " " + alterColStr + ";");
            }

            return list;
        }
        
        #endregion

        #region COLUMNS
        private string GetCreateColumnScript(DataRow r)
        {
            string type = r["DATA_TYPE"].AsString();
            string colStr = "\t" + r["COLUMN_NAME"] + " " + type;
            if (CanSetMaxLength(type))
                colStr += "(" + r["CHARACTER_MAXIMUM_LENGTH"].AsString() + ")";
            if (type == "numeric")
                colStr += "(" + r["NUMERIC_PRECISION"].AsString() + ", " + r["NUMERIC_SCALE"].AsString() + ")";
            if (r["COLUMN_DEFAULT"].IsNotNull())
                colStr += " DEFAULT " + r["COLUMN_DEFAULT"].AsString().EscapeSql();
            if (r["AUTOINC_SEED"].IsNotNull())
                colStr += " IDENTITY (" + r["AUTOINC_SEED"].AsString() + ", " + r["AUTOINC_INCREMENT"].AsString() + ")";
            if (r["IS_NULLABLE"].AsString() == "NO")
                colStr += " NOT NULL";
            else
                colStr += " NULL";
            return colStr;
        }
        private string GetAlterColumnScript(DataRow r)
        {
            string type = r["DATA_TYPE"].AsString();
            string colStr = "";
            if (r.ColumnChanged("DATA_TYPE") || 
                r.ColumnChanged("IS_NULLABLE") || 
                r.ColumnChanged("CHARACTER_MAXIMUM_LENGTH") ||
                r.ColumnChanged("NUMERIC_PRECISION") ||
                r.ColumnChanged("NUMERIC_SCALE"))
            {
                colStr += " " + type;
                if (CanSetMaxLength(type))
                    colStr += "(" + r["CHARACTER_MAXIMUM_LENGTH"].AsString() + ")";
                if (type == "numeric")
                    colStr += "(" + r["NUMERIC_PRECISION"].AsString() + ", " + r["NUMERIC_SCALE"].AsString() + ")";
            }

            if (r.ColumnChanged("AUTOINC_SEED") || r.ColumnChanged("AUTOINC_INCREMENT"))
            {
                colStr += " IDENTITY (" + r["AUTOINC_SEED"].AsString() + ", " + r["AUTOINC_INCREMENT"].AsString() + ")";
            }
            if (r.ColumnChanged("IS_NULLABLE"))
            {
                if (r["IS_NULLABLE"].AsString() == "NO")
                    colStr += " NOT NULL";
                else
                    colStr += " NULL";
            }
            if (colStr.IsNotNull())
                colStr = "ALTER COLUMN " + r["COLUMN_NAME", DataRowVersion.Original].AsString() + " " + colStr;
            return colStr;
        }

        private string GetAlterColumnDropDefaultScript(DataRow r, bool dontCheckModified)
        {
            string type = r["DATA_TYPE"].AsString();
            string colStr = "";

            if (dontCheckModified || (r.ColumnChanged("COLUMN_DEFAULT") && r["COLUMN_DEFAULT"].IsNull()))
            {
                colStr += " DROP DEFAULT ";
            }

            if (colStr.IsNotNull())
                colStr = "ALTER COLUMN " + r["COLUMN_NAME", DataRowVersion.Original].AsString() + " " + colStr;
            return colStr;
        }
        private string GetAlterColumnSetDefaultScript(DataRow r, bool dontCheckModified)
        {
            string colStr = "";

            if (dontCheckModified || r.ColumnChanged("COLUMN_DEFAULT"))
            {
                if (r["COLUMN_DEFAULT"].IsNotNull())
                    colStr += " SET DEFAULT " + r["COLUMN_DEFAULT"].AsString().EscapeSql();
            }

            if (colStr.IsNotNull())
                colStr = "ALTER COLUMN " + r["COLUMN_NAME", DataRowVersion.Original].AsString() + " " + colStr;
            return colStr;
        }

        private DataRow[] GetColumns(string TableName, DataViewRowState state)
        {
            return SchemaDataSet.COLUMNS.Select("TABLE_NAME = " + TableName.EscapeSql(), null, state);
        }
        #endregion

        #region Indexes

        public List<string> GetDropIndexScripts(string table)
        {
            List<string> list = new List<string>();
            string name = "";
            bool unique = false;
            bool pk = false;
            string str = "";
            DataRow[] rows = SchemaDataSet.INDEXES.Select("TABLE_NAME = " + table.EscapeSql(), null, DataViewRowState.CurrentRows | DataViewRowState.Deleted);
            foreach (DataRow r in rows)
            {
                if (r.HasChangesWithreference() && r.RowState != DataRowState.Added)
                {
                    if (r.RowState == DataRowState.Deleted || r.RowState == DataRowState.Modified)
                    {
                        name = r["INDEX_NAME", DataRowVersion.Original].AsString();
                        unique = r["UNIQUE", DataRowVersion.Original].AsBool();
                        pk = r["PRIMARY_KEY", DataRowVersion.Original].AsBool();
                    }
                    else
                    {
                        name = r["INDEX_NAME"].AsString();
                        unique = r["UNIQUE"].AsBool();
                        pk = r["PRIMARY_KEY"].AsBool();
                    }
                    if (pk || unique)
                    {
                        str = "ALTER TABLE " + table + " DROP CONSTRAINT " + name;
                    }
                    else
                    {
                        str = "DROP INDEX " + table + "." + name;
                    }
                    list.Add(str+";");
                }
            }
            return list;
        }

        public List<string> GetCreateIndexScripts(string table)
        {
            List<string> list = new List<string>();
            string name = "";
            bool unique = false;
            bool pk = false;
            string str = "";
            string cols = "";
            DataRow[] columns = null;

            DataRow[] rows = SchemaDataSet.INDEXES.Select("TABLE_NAME = " + table.EscapeSql());
            foreach (DataRow r in rows)
            {
                if (r.HasChangesWithreference() || GenerateCreateScripts)
                {
                    if (r.RowState == DataRowState.Deleted)
                        continue;
                    
                    name = r["INDEX_NAME"].AsString();
                    unique = r["UNIQUE"].AsBool();
                    pk = r["PRIMARY_KEY"].AsBool();

                    columns = SchemaDataSet.INDEXES_COLUMNS.Select("INDEX_NAME = " + name.EscapeSql(), null, DataViewRowState.CurrentRows);
                    cols = "";
                    foreach (DataRow c in columns)
                    {
                        cols = cols.AppendString(", ");
                        cols += c["COLUMN_NAME"].AsString();
                    }
                    if (pk || unique)
                    {
                        str = "ALTER TABLE " + table + " ADD CONSTRAINT " + name + " " + 
                            (pk ? "PRIMARY KEY" : "UNIQUE") ;
                    }
                    else
                    {
                        str = "CREATE INDEX " + name + " ON " + table;
                    }
                    str += " (" + cols + ")";
                    list.Add(str + ";");
                }
            }
            return list;
        }
        #endregion

        #region Foreign Keys

        public List<string> GetDropFkScripts(string table)
        {
            List<string> list = new List<string>();
            string name = "";
            string str = "";
            DataRow[] rows = SchemaDataSet.REFERENTIAL_CONSTRAINTS.Select("CONSTRAINT_TABLE_NAME = " + table.EscapeSql(), null, DataViewRowState.CurrentRows | DataViewRowState.Deleted);
            foreach (DataRow r in rows)
            {
                if (r.HasChangesWithreference() && r.RowState != DataRowState.Added)
                {
                    if (r.RowState == DataRowState.Deleted || r.RowState == DataRowState.Modified)
                    {
                        name = r["CONSTRAINT_NAME", DataRowVersion.Original].AsString();
                    }
                    else
                    {
                        name = r["CONSTRAINT_NAME"].AsString();
                    }
                    str = "ALTER TABLE " + table + " DROP CONSTRAINT " + name;
                    list.Add(str + ";");
                }
            }
            return list;
        }

        public List<string> GetCreateFKScripts(string table)
        {
            List<string> list = new List<string>();
            string name = "";
            string str = "";
            string colsPK = "";
            string colsFK = "";
            string PKTable = "";
            string updateRule = "";
            string deleteRule = "";
            DataRow[] columns = null;

            DataRow[] rows = SchemaDataSet.REFERENTIAL_CONSTRAINTS.Select("CONSTRAINT_TABLE_NAME = " + table.EscapeSql());
            foreach (DataRow r in rows)
            {
                if (r.HasChangesWithreference() || GenerateCreateScripts)
                {
                    if (r.RowState == DataRowState.Deleted)
                        continue;

                    name = r["CONSTRAINT_NAME"].AsString();
                    PKTable = r["UNIQUE_CONSTRAINT_TABLE_NAME"].AsString();
                    updateRule = r["UPDATE_RULE"].AsString();
                    deleteRule = r["DELETE_RULE"].AsString();
                    columns = SchemaDataSet.REFERENTIAL_CONSTRAINTS_COLUMNS.Select("CONSTRAINT_NAME = " + name.EscapeSql(), null, DataViewRowState.CurrentRows);
                    colsPK = "";
                    colsFK = "";
                    foreach (DataRow c in columns)
                    {
                        colsPK = colsPK.AppendString(", ");
                        colsPK += c["PRIMARY_COLUMN"].AsString();

                        colsFK = colsFK.AppendString(", ");
                        colsFK += c["FOREIGN_COLUMN"].AsString();
                    }
                    str = "ALTER TABLE " + table + " ADD CONSTRAINT " + name  +
                       " FOREIGN KEY (" + colsFK + ") REFERENCES " + PKTable + "(" + colsPK + ") ON DELETE " + deleteRule + " ON UPDATE " + updateRule;

                    list.Add(str + ";");
                }
            }
            return list;
        }
        #endregion
        public static bool CanSetMaxLength(string type)
        {
            if (type == "nchar" ||
                type == "nvarchar" ||
                type == "binary" ||
                type == "varbinary")
                return true;
            return false;
        }

        public static string GetXmlDef(string TableName, SchemaDataSet schemaDataSet1, bool copyToClipboard)
        {
            string xml = "";
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.AppendLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
            strBuilder.AppendLine("<xs:schema id=\"NewDataSet\" xmlns=\"\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\" xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" xmlns:msprop=\"urn:schemas-microsoft-com:xml-msprop\">");
            strBuilder.AppendLine("  <xs:annotation>");
            strBuilder.AppendLine("    <xs:appinfo source=\"urn:schemas-microsoft-com:xml-msdatasource\">");
            strBuilder.AppendLine("      <DataSource DefaultConnectionIndex=\"0\" FunctionsComponentName=\"QueriesTableAdapter\" Modifier=\"AutoLayout, AnsiClass, Class, Public\" SchemaSerializationMode=\"IncludeSchema\" xmlns=\"urn:schemas-microsoft-com:xml-msdatasource\">");
            strBuilder.AppendLine("        <ConnectionRefs />");
            strBuilder.AppendLine("        <Connections />");
            strBuilder.AppendLine("        <TableRefs>");
            strBuilder.AppendLine("          <DesignTableRef Name=" + TableName.AddDoubleQuotes() + ">");
            strBuilder.AppendLine("            <ColumnRefs />");
            strBuilder.AppendLine("            <SourceRefs />");
            strBuilder.AppendLine("          </DesignTableRef>");
            strBuilder.AppendLine("        </TableRefs>");
            strBuilder.AppendLine("        <Tables />");
            strBuilder.AppendLine("        <RelationRefs />");
            strBuilder.AppendLine("        <SourceRefs />");
            strBuilder.AppendLine("        <Sources />");
            strBuilder.AppendLine("      </DataSource>");
            strBuilder.AppendLine("    </xs:appinfo>");
            strBuilder.AppendLine("  </xs:annotation>");
            strBuilder.AppendLine("  <xs:element name=\"NewDataSet\" msdata:IsDataSet=\"true\" msdata:UseCurrentLocale=\"true\" msprop:Generator_UserDSName=\"NewDataSet\" msprop:Generator_DataSetName=\"NewDataSet\">");
            strBuilder.AppendLine("    <xs:complexType>");
            strBuilder.AppendLine("      <xs:choice minOccurs=\"0\" maxOccurs=\"unbounded\">");
            strBuilder.AppendLine("        <xs:element name=" + TableName.AddDoubleQuotes() +
                " msprop:Generator_TableClassName=\"" + TableName + "DataTable\" msprop:Generator_TableVarName=\"table" + TableName +
                "\" msprop:Generator_TablePropName=" + TableName.AddDoubleQuotes() +
                    " msprop:Generator_RowDeletingName=\"" + TableName + "RowDeleting\" msprop:Generator_UserTableName=\"" +
                    TableName + "\" msprop:Generator_RowChangingName=\"" + TableName +
                        "RowChanging\" msprop:Generator_RowEvHandlerName=\"" + TableName + "RowChangeEventHandler\" msprop:Generator_RowDeletedName=\"" + TableName +
                        "RowDeleted\" msprop:Generator_RowEvArgName=\"" + TableName +
                        "RowChangeEvent\" msprop:Generator_RowChangedName=\"" + TableName +
                        "RowChanged\" msprop:Generator_RowClassName=\"" + TableName + "Row\">");

            strBuilder.AppendLine("          <xs:complexType>");
            strBuilder.AppendLine("            <xs:sequence>");
            DataRow[] rows = schemaDataSet1.COLUMNS.Select("TABLE_NAME = " + TableName.EscapeSql());
            string colStr ="";
            string type = "";
            string nType ="";
            foreach (DataRow r in rows)
            {
                colStr = "              <xs:element name=\"" + r["COLUMN_NAME"].AsString() + "\"";
                type = r["DATA_TYPE"].AsString();

                if (r["AUTOINC_SEED"].IsNotNull() && type == "int")
                    colStr += " msdata:AutoIncrement=\"true\" msdata:AutoIncrementSeed=\"-1\" msdata:AutoIncrementStep=\"-1\" ";
                colStr += " msprop:Generator_ColumnVarNameInTable=\"column" + r["COLUMN_NAME"].AsString() +
                    "\" msprop:Generator_ColumnPropNameInRow=\"" + r["COLUMN_NAME"].AsString() +
                    "\" msprop:Generator_ColumnPropNameInTable=\"" + r["COLUMN_NAME"].AsString() +
                    "Column\" msprop:Generator_UserColumnName=\"" + r["COLUMN_NAME"].AsString() + "\" type=\"xs:";

                switch (type)
                {
                    case "bigint":
                        nType = "long";
                        break;
                    case "int":
                    case "smallint":
                        nType = "int";
                        break;
                    case "nvarchar":
                    case "ntext":
                    case "nchar":
                        nType = "string";
                        break;
                    case "numeric":
                    case "real":
                        nType = "decimal";
                        break;
                    case "bit":
                        nType = "boolean";
                        break;
                    case "datetime":
                        nType = "dateTime";
                        break;
                    case "binary":
                    case "image":
                    case "varbinary":
                        nType = "base64Binary";
                        break;
                    default:
                        nType = type;
                        break;
                }
                colStr += nType + "\" minOccurs=\"0\" />";
                strBuilder.AppendLine(colStr);
            }
            strBuilder.AppendLine("            </xs:sequence>");
            strBuilder.AppendLine("          </xs:complexType>");
            strBuilder.AppendLine("        </xs:element>");
            strBuilder.AppendLine("      </xs:choice>");
            strBuilder.AppendLine("    </xs:complexType>");
            strBuilder.AppendLine("  </xs:element>");
            strBuilder.AppendLine("</xs:schema>");
            xml = strBuilder.ToString();
            if (copyToClipboard)
            {
                DataObject o = new DataObject();
                o.SetData("CF_PROJITEM", "");
                o.SetData("System.String", xml);
                o.SetData("UnicodeText", xml);
                o.SetData("Text", xml);
                o.SetData("CF_DSI", xml);
                o.SetData("CF_DSI_CONTENT_TYPE", "AllOtherCases");

                Clipboard.SetDataObject(o, true);
            }
            return xml;
        }

        public static List<string> GetSchemaUpdateScripts(SchemaDataSet sourceDataSet, SchemaDataSet destDataSet)
        {
            SchemaDataSet ds = sourceDataSet.Copy() as SchemaDataSet;
            ds.EnforceConstraints = false;

            #region tabele i kolumny
            foreach (DataRow r in destDataSet.TABLES)
            {
                if (!ds.TABLES.RowExists("TABLE_NAME = " + r["TABLE_NAME"].ToString().EscapeSql()))
                {
                    ds.TABLES.AddTABLESRow(r["TABLE_NAME"].ToString());
                }
            }
            DataRow newRow;

            foreach (DataRow r in destDataSet.COLUMNS)
            {
                newRow = ds.COLUMNS.SelectFirstRow("TABLE_NAME = " + r["TABLE_NAME"].ToString().EscapeSql() +
                    " AND COLUMN_NAME = " + r["COLUMN_NAME"].ToString().EscapeSql());
                if (newRow == null)
                {
                    newRow = ds.COLUMNS.NewRow();
                    ds.COLUMNS.Rows.Add(newRow);
                }
                foreach (DataColumn col in sourceDataSet.COLUMNS.Columns)
                {
                    newRow.SetColVal(col.ColumnName, r[col.ColumnName]);
                }
            }
            #endregion
            #region indexy
            foreach (DataRow r in destDataSet.INDEXES)
            {
                newRow = ds.INDEXES.SelectFirstRow("INDEX_NAME = " + r["INDEX_NAME"].ToString().EscapeSql());
                if (newRow == null)
                {
                    newRow = ds.INDEXES.NewRow();
                    ds.INDEXES.Rows.Add(newRow);
                }
                foreach (DataColumn col in sourceDataSet.INDEXES.Columns)
                {
                    newRow.SetColVal(col.ColumnName, r[col.ColumnName]);
                }
            }
            foreach (DataRow r in destDataSet.INDEXES_COLUMNS)
            {
                newRow = ds.INDEXES_COLUMNS.SelectFirstRow("INDEX_NAME = " + r["INDEX_NAME"].ToString().EscapeSql() + " AND COLUMN_NAME = " + r["COLUMN_NAME"].AsString().EscapeSql());
                if (newRow == null)
                {
                    newRow = ds.INDEXES_COLUMNS.NewRow();
                    ds.INDEXES_COLUMNS.Rows.Add(newRow);
                }
                foreach (DataColumn col in sourceDataSet.INDEXES_COLUMNS.Columns)
                {
                    newRow.SetColVal(col.ColumnName, r[col.ColumnName]);
                }
            }
            #endregion
            #region REFERENTIAL_CONSTRAINTS
            foreach (DataRow r in destDataSet.REFERENTIAL_CONSTRAINTS)
            {
                newRow = ds.REFERENTIAL_CONSTRAINTS.SelectFirstRow("CONSTRAINT_NAME = " + r["CONSTRAINT_NAME"].ToString().EscapeSql());
                if (newRow == null)
                {
                    newRow = ds.REFERENTIAL_CONSTRAINTS.NewRow();
                    ds.REFERENTIAL_CONSTRAINTS.Rows.Add(newRow);
                }
                foreach (DataColumn col in sourceDataSet.REFERENTIAL_CONSTRAINTS.Columns)
                {
                    newRow.SetColVal(col.ColumnName, r[col.ColumnName]);
                }
            }

            foreach (DataRow r in destDataSet.REFERENTIAL_CONSTRAINTS_COLUMNS)
            {
                newRow = ds.REFERENTIAL_CONSTRAINTS_COLUMNS.SelectFirstRow("CONSTRAINT_NAME = " + r["CONSTRAINT_NAME"].ToString().EscapeSql()
                    + " AND PRIMARY_COLUMN = " + r["PRIMARY_COLUMN"].AsString().EscapeSql());
                if (newRow == null)
                {
                    newRow = ds.REFERENTIAL_CONSTRAINTS_COLUMNS.NewRow();
                    ds.REFERENTIAL_CONSTRAINTS_COLUMNS.Rows.Add(newRow);
                }
                foreach (DataColumn col in sourceDataSet.REFERENTIAL_CONSTRAINTS_COLUMNS.Columns)
                {
                    newRow.SetColVal(col.ColumnName, r[col.ColumnName]);
                }
            }
            #endregion

            #region kasowanie starych kolumn, indexow itp
            #region tabele i kolumny
            DataRow[] rows = ds.TABLES.Select();

            foreach (DataRow r in rows)
            {
                if (!destDataSet.TABLES.RowExists("TABLE_NAME = " + r["TABLE_NAME"].ToString().EscapeSql()))
                    r.Delete();
            }
            rows = ds.COLUMNS.Select();
            foreach (DataRow r in rows)
            {
                newRow = ds.COLUMNS.SelectFirstRow("TABLE_NAME = " + r["TABLE_NAME"].ToString().EscapeSql() +
                    " AND COLUMN_NAME = " + r["COLUMN_NAME"].ToString().EscapeSql());
                if (newRow == null)
                    r.Delete();
            }
            #endregion
            #region indexy
            rows = ds.INDEXES.Select();
            foreach (DataRow r in rows)
            {
                newRow = ds.INDEXES.SelectFirstRow("INDEX_NAME = " + r["INDEX_NAME"].ToString().EscapeSql());
                if (newRow == null)
                    r.Delete();
            }
            rows = ds.INDEXES_COLUMNS.Select();
            foreach (DataRow r in rows)
            {
                newRow = ds.INDEXES_COLUMNS.SelectFirstRow("INDEX_NAME = " + r["INDEX_NAME"].ToString().EscapeSql() + " AND COLUMN_NAME = " + r["COLUMN_NAME"].AsString().EscapeSql());
                if (newRow == null)
                    r.Delete();
            }
            #endregion
            #region REFERENTIAL_CONSTRAINTS
            rows = ds.REFERENTIAL_CONSTRAINTS.Select();
            foreach (DataRow r in rows)
            {
                newRow = ds.REFERENTIAL_CONSTRAINTS.SelectFirstRow("CONSTRAINT_NAME = " + r["CONSTRAINT_NAME"].ToString().EscapeSql());
                if (newRow == null)
                    r.Delete();
            }
            rows = ds.REFERENTIAL_CONSTRAINTS_COLUMNS.Select();
            foreach (DataRow r in rows)
            {
                newRow = ds.REFERENTIAL_CONSTRAINTS_COLUMNS.SelectFirstRow("CONSTRAINT_NAME = " + r["CONSTRAINT_NAME"].ToString().EscapeSql()
                    + " AND PRIMARY_COLUMN = " + r["PRIMARY_COLUMN"].AsString().EscapeSql());
                if (newRow == null)
                    r.Delete();
            }
            #endregion
            #endregion
            ds.EnforceConstraints = true;

            ScriptTools s = new ScriptTools(ds);
            return s.GetScripts();
        }
    }
}

